package com.resto.scm.web.datasource;


import com.resto.common.dao.jdbc.param.GenerateUtil;
import com.resto.common.dao.jdbc.param.ISqlParam;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Component("dbHelper")
public class DBHelper extends DynamicDataSource {

    private static Logger LOG = LoggerFactory.getLogger(DBHelper.class);




    public static void buildInParam(StringBuffer sql, int paramCount) {
        sql.append("(");
        if (paramCount == 1) {
            sql.append("?");
        } else {
            int last = paramCount - 1;
            for (int i = 0; i < paramCount; i++) {
                if (i == last) {
                    sql.append("?");
                } else {
                    sql.append("?,");
                }
            }
        }
        sql.append(")");
    }

    public static void buildInParam(StringBuilder sql, int paramCount) {
        sql.append("(");
        if (paramCount == 1) {
            sql.append("?");
        } else {
            int last = paramCount - 1;
            for (int i = 0; i < paramCount; i++) {
                if (i == last) {
                    sql.append("?");
                } else {
                    sql.append("?,");
                }
            }
        }
        sql.append(")");
    }

    public void executeSql(String sql) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            if (StringUtils.isNotBlank(sql)) {
                if (sql.trim().startsWith("delete")) {
                    throw new RuntimeException("不允许物理删除!");
                }
                if (sql.contains("update") && !sql.contains("where")) {
                    throw new RuntimeException("更新必须有where条件");
                }
                connection = getDataSource().getConnection();

                System.out.println(connection);
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            LOG.error(e.getMessage(), e);
            throw new RuntimeException(e.getMessage());
        } finally {
            close(preparedStatement, connection);
        }
    }

    public List<Object> query(String sql) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Object> result;
        HashMap<String, Object> columnMap = new HashMap<String, Object>();
        try {
            connection = getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            Map<String, String> columnNameMap = new HashMap<String, String>();
            for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                String fieldProperty = getClassFieldProperty(resultSetMetaData.getColumnLabel(i).toLowerCase());
                columnMap.put(fieldProperty, Class.forName(resultSetMetaData.getColumnClassName(i)));
                columnNameMap.put(fieldProperty, resultSetMetaData.getColumnLabel(i).toLowerCase());
            }
            Object dynamicBean = new DynamicBean(columnMap).getObject();
            result = GenerateUtil.generateObjectListFromResultSet(dynamicBean.getClass(), columnNameMap, resultSet);
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            throw new RuntimeException(e.getMessage());
        } finally {
            close(resultSet, preparedStatement, connection);
        }
        return result;
    }

    public List<Object> query(String sql, String... parameters) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Object> result;
        HashMap<String, Object> columnMap = new HashMap<String, Object>();
        try {
            connection =getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (parameters != null) {
                for (int i = 0; i < parameters.length; i++) {
                    preparedStatement.setString(i + 1, parameters[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            Map<String, String> columnNameMap = new HashMap<String, String>();
            for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                String fieldProperty = getClassFieldProperty(resultSetMetaData.getColumnLabel(i).toLowerCase());
                columnMap.put(fieldProperty, Class.forName(resultSetMetaData.getColumnClassName(i)));
                columnNameMap.put(fieldProperty, resultSetMetaData.getColumnLabel(i).toLowerCase());
            }
            Object dynamicBean = new DynamicBean(columnMap).getObject();
            result = GenerateUtil.generateObjectListFromResultSet(dynamicBean.getClass(), columnNameMap, resultSet);
        } catch (Exception e) {
            LOG.error("", e);
            throw new RuntimeException(e.getMessage());
        } finally {
            close(resultSet, preparedStatement, connection);
        }
        return result;
    }


    public List<Object> query(String sql, Map<String, ISqlParam> parameters) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Object> result;
        HashMap<String, Object> columnMap = new HashMap<String, Object>();
        try {
            connection = getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if (parameters != null) {
                for (Map.Entry<String, ISqlParam> paramEntry : parameters.entrySet()) {
                    ISqlParam sqlParam = paramEntry.getValue();
                    sqlParam.setPstParameter(preparedStatement);
                }
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            Map<String, String> columnNameMap = new HashMap<String, String>();
            for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                String fieldProperty = getClassFieldProperty(resultSetMetaData.getColumnLabel(i).toLowerCase());
                columnMap.put(fieldProperty, Class.forName(resultSetMetaData.getColumnClassName(i)));
                columnNameMap.put(fieldProperty, resultSetMetaData.getColumnLabel(i).toLowerCase());
            }
            Object dynamicBean = new DynamicBean(columnMap).getObject();
            result = GenerateUtil.generateObjectListFromResultSet(dynamicBean.getClass(), columnNameMap, resultSet);
        } catch (Exception e) {
            LOG.error("", e);
            throw new RuntimeException(e.getMessage());
        } finally {
            close(resultSet, preparedStatement, connection);
        }
        return result;
    }

    public List<Object> query(String sql, List<ISqlParam> parameters) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Object> result;
        HashMap<String, Object> columnMap = new HashMap<String, Object>();
        try {
            connection =getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (parameters != null && parameters.size() > 0) {
                for (ISqlParam sqlParam : parameters) {
                    sqlParam.setPstParameter(preparedStatement);
                }
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            Map<String, String> columnNameMap = new HashMap<String, String>();
            for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                String fieldProperty = getClassFieldProperty(resultSetMetaData.getColumnLabel(i).toLowerCase());
                columnMap.put(fieldProperty, Class.forName(resultSetMetaData.getColumnClassName(i)));
                columnNameMap.put(fieldProperty, resultSetMetaData.getColumnLabel(i).toLowerCase());
            }
            Object dynamicBean = new DynamicBean(columnMap).getObject();
            result = GenerateUtil.generateObjectListFromResultSet(dynamicBean.getClass(), columnNameMap, resultSet);
        } catch (Exception e) {
            LOG.error("", e);
            throw new RuntimeException(e.getMessage());
        } finally {
            close(resultSet, preparedStatement, connection);
        }
        return result;
    }

    /**
     * 统计记录总数
     *
     * @param sql
     * @return
     */
    public int count(String sql) {
        int count = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
            throw new RuntimeException(e.getMessage());
        } finally {
            close(resultSet, preparedStatement, connection);
        }
        return count;
    }

    public int count(String sql, Map<String, ISqlParam> parameters) {
        int count = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if (parameters != null) {
                for (Map.Entry<String, ISqlParam> paramEntry : parameters.entrySet()) {
                    String key = paramEntry.getKey();
                    if (key.equals("startRow") || key.equals("pageSize") || key.equals("page")) {
                        continue;
                    }
                    ISqlParam sqlParam = paramEntry.getValue();
                    sqlParam.setPstParameter(preparedStatement);
                }
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }
            return count;

        } catch (Exception e) {
            LOG.error("", e);
            throw new RuntimeException(e.getMessage());
        } finally {
            close(resultSet, preparedStatement, connection);
        }
    }

    public int count(String sql, List<ISqlParam> params) {
        int count = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if (params != null && params.size() > 0) {
                for (ISqlParam sqlParam : params) {
                    sqlParam.setPstParameter(preparedStatement);
                }
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }
            return count;
        } catch (Exception e) {
            LOG.error("", e);
            throw new RuntimeException(e.getMessage());
        } finally {
            close(resultSet, preparedStatement, connection);
        }
    }


    /**
     * 关闭数据源
     *
     * @param resultSet
     * @param statement
     * @param connection
     */
    private void close(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                LOG.error(e.getMessage(), e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e) {
                LOG.error(e.getMessage(), e);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
                LOG.error(e.getMessage(), e);
            }
        }
    }

    /**
     * 关闭数据源
     *
     * @param statement
     * @param connection
     */
    private void close(Statement statement, Connection connection) {
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e) {
                LOG.error(e.getMessage(), e);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
                LOG.error(e.getMessage(), e);
            }
        }
    }

    private String getClassFieldProperty(String column) {
        if (StringUtils.isBlank(column)) {
            return column;
        }
        StringBuilder fieldBuild = new StringBuilder();
        column = column.toLowerCase();
        for (int i = 0; i < column.length(); i++) {
            if (column.charAt(i) == '_' && i != column.length() - 1) {
                String cr = String.valueOf(column.charAt(i + 1)).toUpperCase();
                fieldBuild.append(cr);
                i++;
            } else {
                fieldBuild.append(column.charAt(i));
            }
        }
        return fieldBuild.toString().replace("_", "");
    }

    @Override
    public void afterPropertiesSet() {

    }
}
